﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Reports.GoodsTransfer
{
    public class DataLoader
    {
        /// <summary>
        /// Возвращает данные для движения товара
        /// </summary>
        /// <param name="DepName">название аптеки</param>
        /// <param name="DateF">дата начала</param>
        /// <param name="dateT">дата окончания</param>
        /// <param name="ConnectionString">строка подключения</param>
        /// <returns></returns>
        public static void GetData(Reports.GoodsTransfer.DataSet.DataDataTable data, string ConnectionString, string DepName, DateTime DateF, DateTime DateT,Int32? goodsId,string depFiltr)
        {
            #region строка запроса
            string QueryString =
@"SELECT     ISNULL(CO.Departments.Name,'') AS DepartName, vwGoodsDetailed.FullName AS GoodsName, CD.Parties.GoodsId AS GoodsCode, 
CA.GoodsDocLines.RetailSum /CA.GoodsDocLines.Qntty AS Price,
--CD.Parties.RetailPrice AS Price, 
                      CD.Parties.BarCode AS Barcode, CA.Contragents.ShortName AS Contractor,
                          (SELECT     ISNULL(SUM(Qntty), 0) AS sum
                            FROM          vwGoodsFlow AS vwgi
                            WHERE      (DocDate < @DateF) AND (PartyId = CD.Parties.Id)) AS StartCount,
                          (SELECT     ISNULL(SUM(Qntty), 0) AS sum
                            FROM          vwGoodsFlow AS vwgi
                            WHERE      (DocDate < @DateT) AND (PartyId = CD.Parties.Id)) AS EndCount, CASE WHEN Qntty > 0 THEN Qntty ELSE 0 END AS IncomeCount, 
                      CASE WHEN Qntty < 0 THEN Qntty * - 1 ELSE 0 END AS ExpenceCount, CA.Documents.DocNumber, CO.DocTypes.ShortName AS DocType, 
                      CA.Documents.DocDate, CA.Documents.DocTypeId AS DocTypeCode
FROM         CA.GoodsDocLines INNER JOIN
                      CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id INNER JOIN
                      CD.Parties ON CA.GoodsDocLines.PartyId = CD.Parties.Id INNER JOIN
                      CO.DocTypes ON CA.Documents.DocTypeId = CO.DocTypes.Id INNER JOIN
                      CA.Contragents ON CA.Documents.ContragentId = CA.Contragents.Id INNER JOIN
                      vwGoodsDetailed ON vwGoodsDetailed.Id = CD.Parties.GoodsId LEFT OUTER JOIN
                      CO.Departments ON CA.Documents.DepartmentId = CO.Departments.Id
WHERE     (CA.Documents.DocDate >= @DateF AND CA.Documents.DocDate < @DateT) {0}
ORDER BY DepartName, GoodsName, Barcode";
            string QueryString1 =
@"SELECT    ISNULL(CO.Departments.Name,'') AS DepartName, vwGoodsDetailed.FullName AS GoodsName, CD.Parties.GoodsId AS GoodsCode, 
CA.GoodsDocLines.RetailSum /CA.GoodsDocLines.Qntty AS Price,
--CD.Parties.RetailPrice AS Price, 
                      CD.Parties.BarCode AS Barcode, CA.Contragents.ShortName AS Contractor,
                          (SELECT     ISNULL(SUM(Qntty), 0) AS sum
                            FROM          vwGoodsFlow AS vwgi
                            WHERE      (DocDate < @DateF) AND (PartyId = CD.Parties.Id)) AS StartCount,
                          (SELECT     ISNULL(SUM(Qntty), 0) AS sum
                            FROM          vwGoodsFlow AS vwgi
                            WHERE      (DocDate < @DateT) AND (PartyId = CD.Parties.Id)) AS EndCount, CASE WHEN Qntty > 0 THEN Qntty ELSE 0 END AS IncomeCount, 
                      CASE WHEN Qntty < 0 THEN Qntty * - 1 ELSE 0 END AS ExpenceCount, CA.Documents.DocNumber, CO.DocTypes.ShortName AS DocType, 
                      CA.Documents.DocDate, CA.Documents.DocTypeId AS DocTypeCode
FROM         CA.GoodsDocLines INNER JOIN
                      CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id INNER JOIN
                      CD.Parties ON CA.GoodsDocLines.PartyId = CD.Parties.Id INNER JOIN
                      CO.DocTypes ON CA.Documents.DocTypeId = CO.DocTypes.Id INNER JOIN
                      CA.Contragents ON CA.Documents.ContragentId = CA.Contragents.Id INNER JOIN
                      vwGoodsDetailed ON vwGoodsDetailed.Id = CD.Parties.GoodsId LEFT OUTER JOIN
                      CO.Departments ON CA.Documents.DepartmentId = CO.Departments.Id
WHERE     (CA.Documents.DocDate >= @DateF AND CA.Documents.DocDate < @DateT) AND CD.Parties.GoodsId = @GoodsId {0}
ORDER BY DepartName, GoodsName, Barcode";
            string QueryString2 = @"DECLARE @tab TABLE(pId int primary key)
insert into @tab
SELECT DISTINCT  CA.GoodsDocLines.Id
FROM CA.GoodsDocLines INNER JOIN
CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id
WHERE     (CA.Documents.DocDate BETWEEN @DateF AND @DateT)
OR CA.GoodsDocLines.Id IN
/*(SELECT     CA.GoodsDocLines.Id
FROM         CA.GoodsDocLines INNER JOIN
                      CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id INNER JOIN
                      CD.Parties ON CA.GoodsDocLines.PartyId = CD.Parties.Id
WHERE     (CA.Documents.DocDate < @DateF) AND (CD.Parties.RestQntty > 0))*/
(SELECT  GodsDocLinesId 
FROM         dbo.vwGoodsFlow
WHERE     DocDate < @DateF and DocTypeId in (2,5)
GROUP BY GodsDocLinesId 
HAVING      (SUM(Qntty) > 0))
SELECT     ISNULL(CO.Departments.Name,'***') AS DepartName, 
vwGoodsDetailed.FullName AS GoodsName, CD.Parties.GoodsId AS 
GoodsCode, /*CD.Parties.RetailPrice*/ CA.GoodsDocLines.RetailSum/CA.GoodsDocLines.Qntty AS Price, 
CD.Parties.BarCode AS Barcode, CA.Contragents.ShortName AS Contractor,
(
select isnull(sum(g.Qntty),0)
from CA.GoodsDocLines g inner join CA.Documents d
on d.Id = g.DocId
where g.PartyId = CD.Parties.Id and d.docDate < @DateF
)AS StartCount,
(
select isnull(sum(g.Qntty),0)
from CA.GoodsDocLines g inner join CA.Documents d
on d.Id = g.DocId
where g.PartyId = CD.Parties.Id and d.docDate < @DateT
) AS EndCount,
(
select isnull(sum(g.Qntty),0)
from CA.GoodsDocLines g inner join CA.Documents d
on d.Id = g.DocId
where g.PartyId = CD.Parties.Id and d.docDate BETWEEN @DateF AND @DateT
AND g.Qntty > 0 AND d.Id = CA.Documents.Id
)  AS IncomeCount,

CASE WHEN CA.GoodsDocLines.Qntty < 0 THEN CA.GoodsDocLines.Qntty * - 1 ELSE 0 END AS ExpenceCount, 
CA.Documents.DocNumber, CO.DocTypes.ShortName AS DocType, 
CA.Documents.DocDate, CA.Documents.DocTypeId AS DocTypeCode,
CD.Parties.RetailPrice as Price1,
CA.GoodsDocLines.DiscountValue as Discount
FROM   CA.GoodsDocLines INNER JOIN
CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id INNER JOIN
CD.Parties ON CA.GoodsDocLines.PartyId = CD.Parties.Id INNER JOIN
CO.DocTypes ON CA.Documents.DocTypeId = CO.DocTypes.Id INNER JOIN
CA.Contragents ON CA.Documents.ContragentId = CA.Contragents.Id INNER JOIN
vwGoodsDetailed ON vwGoodsDetailed.Id = CD.Parties.GoodsId LEFT OUTER JOIN
CO.Departments ON CA.Documents.DepartmentId = CO.Departments.Id
WHERE     
CA.GoodsDocLines.Id in (Select pId from @tab) and
(CA.Documents.DocDate < @DateT) {0}
ORDER BY DepartName, GoodsName, Barcode
";
            string QueryString3 = @"DECLARE @tab TABLE(pId int primary key)
insert into @tab
SELECT DISTINCT  CA.GoodsDocLines.Id
FROM CA.GoodsDocLines INNER JOIN
CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id
WHERE     (CA.Documents.DocDate BETWEEN @DateF AND @DateT)
OR CA.GoodsDocLines.Id IN
/*(SELECT     CA.GoodsDocLines.Id
FROM         CA.GoodsDocLines INNER JOIN
                      CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id INNER JOIN
                      CD.Parties ON CA.GoodsDocLines.PartyId = CD.Parties.Id
WHERE     (CA.Documents.DocDate < @DateF) AND (CD.Parties.RestQntty > 0))*/
(SELECT  GodsDocLinesId
FROM         dbo.vwGoodsFlow
WHERE     DocDate < @DateF and DocTypeId in (2,5)
GROUP BY GodsDocLinesId
HAVING      (SUM(Qntty) > 0))
SELECT     ISNULL(CO.Departments.Name,'***') AS DepartName, 
vwGoodsDetailed.FullName AS GoodsName, CD.Parties.GoodsId AS 
GoodsCode,  CA.GoodsDocLines.RetailSum/CA.GoodsDocLines.Qntty AS Price, 

CD.Parties.BarCode AS Barcode, CA.Contragents.ShortName AS Contractor,
(
select isnull(sum(g.Qntty),0)
from CA.GoodsDocLines g inner join CA.Documents d
on d.Id = g.DocId
where g.PartyId = CD.Parties.Id and d.docDate < @DateF
)AS StartCount,
(
select isnull(sum(g.Qntty),0)
from CA.GoodsDocLines g inner join CA.Documents d
on d.Id = g.DocId
where g.PartyId = CD.Parties.Id and d.docDate < @DateT
) AS EndCount,
(
select isnull(sum(g.Qntty),0)
from CA.GoodsDocLines g inner join CA.Documents d
on d.Id = g.DocId
where g.PartyId = CD.Parties.Id and d.docDate BETWEEN @DateF AND @DateT
AND g.Qntty > 0 AND d.Id = CA.Documents.Id
)  AS IncomeCount,

CASE WHEN CA.GoodsDocLines.Qntty < 0 THEN CA.GoodsDocLines.Qntty * - 1 ELSE 0 END AS ExpenceCount, 
CA.Documents.DocNumber, CO.DocTypes.ShortName AS DocType, 
CA.Documents.DocDate, CA.Documents.DocTypeId AS DocTypeCode,
CD.Parties.RetailPrice as Price1,
CA.GoodsDocLines.DiscountValue as Discount
FROM   CA.GoodsDocLines INNER JOIN
CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id INNER JOIN
CD.Parties ON CA.GoodsDocLines.PartyId = CD.Parties.Id INNER JOIN
CO.DocTypes ON CA.Documents.DocTypeId = CO.DocTypes.Id INNER JOIN
CA.Contragents ON CA.Documents.ContragentId = CA.Contragents.Id INNER JOIN
vwGoodsDetailed ON vwGoodsDetailed.Id = CD.Parties.GoodsId LEFT OUTER JOIN
CO.Departments ON CA.Documents.DepartmentId = CO.Departments.Id
WHERE     
CA.GoodsDocLines.Id in (Select pId from @tab) and
(CA.Documents.DocDate < @DateT) AND CD.Parties.GoodsId = @GoodsId {0}
ORDER BY DepartName, GoodsName, Barcode
";
            #endregion
            try
            {
                data.Clear();
                data.AcceptChanges();
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    SqlCommand command;
                    if (goodsId.HasValue)
                        command = new SqlCommand(string.Format(QueryString3,depFiltr), connection);
                    else
                        command = new SqlCommand(string.Format(QueryString2,depFiltr), connection);
                    SqlParameter depName = new SqlParameter("@DepartName", SqlDbType.VarChar, 50);
                    depName.Value = DepName;
                    command.Parameters.Add(depName);
                    SqlParameter dateF = new SqlParameter("@DateF", SqlDbType.DateTime);
                    dateF.Value = DateF;
                    command.Parameters.Add(dateF);
                    SqlParameter dateT = new SqlParameter("@DateT", SqlDbType.DateTime);
                    dateT.Value = DateT;
                    command.Parameters.Add(dateT);
                    if (goodsId.HasValue)
                    {
                        SqlParameter GoodsId = new SqlParameter("@GoodsId", SqlDbType.Int);
                        GoodsId.Value = goodsId.Value;
                        command.Parameters.Add(GoodsId);
                    }
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(data);
                }
            }
            catch (SqlException se)
            {
                System.Windows.Forms.MessageBox.Show(se.Message);
            }
            catch (Exception ee)
            {
                System.Windows.Forms.MessageBox.Show(ee.Message);
            }
        }
    }
}
